﻿using System;
using System.Linq;
using System.Data;
using System.Collections.Generic;


namespace SquidConsult.DAL
{   
    public class PeopleDS
    {  
        /// <summary>
        /// this class store common Data access fuctions and procedures  for accoutant, constant 
        /// and supervisor to inherit
        /// </summary>
        protected SquidConsultantEntity context = new SquidConsultantEntity();

        #region Get Expense
        public IQueryable GetExpense(Guid reportId)
        {
            var exps = from exp in context.expenses
                       where exp.ReportID == reportId
                       orderby exp.ExpenseDate
                       select new
                          {
                              exp.ExpenseID,
                              exp.ExpenseDate,
                              exp.Location,
                              exp.Description,
                              exp.Expense
                          };
            return exps;
        }
        

        public string GetReceiptPath(Guid expenseId)
        {
            var exp = context.expenses.Single(e => e.ExpenseID == expenseId);

            return exp.ReceiptPath;
        }
        #endregion

        #region Get Budget
        public double GetDepartmentTotalExpense(string supervisorName)
        {
            //get login supervisor's department name 
            var dep = context.supervisors.Single(d => d.SupervisorName == supervisorName);

            //get  department supervisors 
            var subQuery = from sup in context.supervisors
                           where sup.Department == dep.Department
                           select sup.SupervisorName;

            //get  department approved reports
            var reports = from rep in context.reports
                          where rep.Status_Account == "Approved"
                          && rep.Status_Supervisor == "Approved"
                          && subQuery.Contains(rep.SupervisorName)
                          select rep;

            //get department total expense 
            if (reports.Count() == 0)
                return 0;
            else
                return double.Parse(reports.Sum(r => r.TotalExpense).ToString());
        }

        public double GetDepartmentBudget(string supervisorName)
        {
            var supervisor = context.supervisors.Single(s => s.SupervisorName == supervisorName);

            var budget = context.departments.Single(d => d.DepartmentName == supervisor.Department);

            return double.Parse(budget.Budget.ToString());
        }
        #endregion
    }
}
